using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
namespace ProcessesSMS
{
	/// <summary>
	/// Summary description for SMS_STR.
	/// </summary>
	
	public class SMS_Limit
	{								
		public SMS_Limit()
		{
			//			
			//
		}	
		public string OverLimit (string UserID, string CommandCode, string ServiceID)
		{
			string res = "";
			SqlConnection conn;
			string connstr = ConfigSettings.ReadSetting("SMSConnStr");
			conn = new SqlConnection(connstr);
			SqlCommand cmd;
			string reqSQL;

			SMS_STR smsSTR = new SMS_STR();
			string UserNumber = smsSTR.ValidMobileNum(UserID);
			if (UserNumber.Substring(0,4)=="8490" || UserNumber.Substring(0,4)=="8493"|| UserNumber.Substring(0,4)=="84121"|| UserNumber.Substring(0,4)=="84122"|| UserNumber.Substring(0,4)=="84126") //MobiFone
			{
				reqSQL = "SELECT ServiceID, COUNT(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID='" + UserNumber + "' GROUP BY ServiceID";

				conn.Open();
				cmd = new SqlCommand(reqSQL,conn);
				int TotalValue = 0;
				try
				{
					SqlDataReader sqlDR= cmd.ExecuteReader();
					if (sqlDR.HasRows)//Co du lieu
					{
						while (sqlDR.Read())
						{
							ServiceID = sqlDR[0].ToString();
							if (ServiceID.IndexOf("6072")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 500;
							}
							if (ServiceID.IndexOf("6172")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 1000;
							}
							if (ServiceID.IndexOf("6272")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 2000;
							}						
							if (ServiceID.IndexOf("6372")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 3000;
							}						
							if (ServiceID.IndexOf("6472")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 4000;
							}							
							if (ServiceID.IndexOf("6572")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 5000;
							}							
							if (ServiceID.IndexOf("6672")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 10000;
							}							
							if (ServiceID.IndexOf("6772")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 15000;
							}		
						}                  
						if (TotalValue > 150000)
							res="Ban da vuot qua gioi han tin nhan trong ngay";
					}
					sqlDR.Close();
				}
				catch (Exception ex)
				{
					ServiceID = ex.Message;
				}   
				finally
				{
					cmd.Dispose();
					conn.Close();
				}
			}//End Mobi

			else if (UserNumber.Substring(0,4)=="8491" || UserNumber.Substring(0,4)=="8494"|| UserNumber.Substring(0,4)=="84123"|| UserNumber.Substring(0,4)=="84125") //Vinaphone
			{
				//Kiem tra 5 phut
				reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-5).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
				reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-5).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;
				conn.Open();
				cmd = new SqlCommand(reqSQL,conn);
				try
				{
					SqlDataReader sqlDR= cmd.ExecuteReader();
					if (sqlDR.HasRows)//Co du lieu
					{
						sqlDR.Read();
						if (int.Parse(sqlDR[0].ToString()) > 3)
						{

							res = "Ban da vuot qua gioi han tin nhan trong 5 phut";
							cmd.Dispose();
							conn.Close();
							sqlDR.Close();
							return res;
						}
					}
					sqlDR.Close();
				}
				catch (Exception ex)
				{
					ServiceID = ex.Message;
				}   
				finally
				{
					
					cmd.Dispose();

				}

				//Kiem tra 10 phut
				reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-10).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
				reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-10).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

				cmd = new SqlCommand(reqSQL,conn);
				try
				{
					SqlDataReader sqlDR= cmd.ExecuteReader();
					if (sqlDR.HasRows)//Co du lieu
					{
						sqlDR.Read();
						if (int.Parse(sqlDR[0].ToString()) > 5)
						{
							res = "Ban da vuot qua gioi han tin nhan trong 10 phut";
							cmd.Dispose();
							conn.Close();
							sqlDR.Close();
							return res;
						}
					}
					sqlDR.Close();
				}
				catch (Exception ex)
				{
					ServiceID = ex.Message;
				}   
				finally
				{
					
					cmd.Dispose();

				}

				//Kiem tra 60 phut
				reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-60).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
				reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-60).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

				cmd = new SqlCommand(reqSQL,conn);
				try
				{
					SqlDataReader sqlDR= cmd.ExecuteReader();
					if (sqlDR.HasRows)//Co du lieu
					{
							sqlDR.Read();

						if (int.Parse(sqlDR[0].ToString()) > 30)
						{
							res = "Ban da vuot qua gioi han tin nhan trong 60 phut";
							cmd.Dispose();
							conn.Close();
							sqlDR.Close();
							return res;
						}
					}
					sqlDR.Close();
				}
				catch (Exception ex)
				{
					ServiceID = ex.Message;
				}   
				finally
				{
					
					cmd.Dispose();

				}

				//Kiem tra mot ngay 
				reqSQL = "(SELECT MO_ID From SMS_MT WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
				reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

				cmd = new SqlCommand(reqSQL,conn);
				try
				{
					SqlDataReader sqlDR= cmd.ExecuteReader();
					if (sqlDR.HasRows)//Co du lieu
					{
							sqlDR.Read();

						if (int.Parse(sqlDR[0].ToString()) > 300)
						{
							res = "Ban da vuot qua gioi han tin nhan trong ngay";
							cmd.Dispose();
							conn.Close();
							sqlDR.Close();
							return res;
						}
					}
					sqlDR.Close();
				}
				catch (Exception ex)
				{
					ServiceID = ex.Message;
				}   
				finally
				{
					
					cmd.Dispose();
					conn.Close();
				}
				//int TotalValue = 0;
				//reqSQL = "SELECT ServiceID, COUNT(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID='" + UserNumber + "' GROUP BY ServiceID";

			}//end Vina

			//if (UserNumber.Substring(0,4) == "8497" || UserNumber.Substring(0,4) == "8498" || UserNumber.Substring(0,5) == "84168" ) //Viettel
			else //Viettel - Xu ly tat ca cac dau so con lai
			{
				if (ServiceID == "6072" || ServiceID == "6172" || ServiceID == "6272" || ServiceID == "6372" || ServiceID == "6472" || ServiceID == "6572") //Cac tin nhan duoi 10.000
				{
					//Kiem tra 5 phut
					reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-5).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
					reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-5).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;
					conn.Open();
					cmd = new SqlCommand(reqSQL,conn);
					try
					{
						SqlDataReader sqlDR= cmd.ExecuteReader();
						if (sqlDR.HasRows)//Co du lieu
						{
							sqlDR.Read();
							if (int.Parse(sqlDR[0].ToString()) > 3)
							{
								res = "Ban da vuot qua gioi han tin nhan trong 5 phut";
								cmd.Dispose();
								conn.Close();
								sqlDR.Close();
								return res;
							}
						}
						sqlDR.Close();
					}
					catch (Exception ex)
					{
						ServiceID = ex.Message;
					}   
					finally
					{
					
						cmd.Dispose();

					}

					//Kiem tra 60 phut
					reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-60).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
					reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-60).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

					cmd = new SqlCommand(reqSQL,conn);
					try
					{
						SqlDataReader sqlDR= cmd.ExecuteReader();
						if (sqlDR.HasRows)//Co du lieu
						{
							sqlDR.Read();
							if (int.Parse(sqlDR[0].ToString()) > 10)
							{
								res = "Ban da vuot qua gioi han tin nhan trong 60 phut";
								cmd.Dispose();
								conn.Close();
								sqlDR.Close();
								return res;
							}
						}
						sqlDR.Close();
					}
					catch (Exception ex)
					{
						ServiceID = ex.Message;
					}   
					finally
					{
					
						cmd.Dispose();

					}

					//Kiem tra mot ngay 
					reqSQL = "(SELECT MO_ID From SMS_MT WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
					reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

					cmd = new SqlCommand(reqSQL,conn);
					try
					{
						SqlDataReader sqlDR= cmd.ExecuteReader();
						if (sqlDR.HasRows)//Co du lieu
						{
							sqlDR.Read();
							if (int.Parse(sqlDR[0].ToString()) > 100)
							{
								res = "Ban da vuot qua gioi han tin nhan trong ngay";
								cmd.Dispose();
								conn.Close();
								sqlDR.Close();
								return res;
							}
						}
						sqlDR.Close();
					}
					catch (Exception ex)
					{
						ServiceID = ex.Message;
					}   
					finally
					{
					
						cmd.Dispose();
						conn.Close();
					}
				}
				else //Cac tin nhan gia tri tu 10000 tro len
				{
					//Kiem tra 5 phut
					reqSQL = "(SELECT MO_ID From SMS_MT WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-30).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
					reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-30).ToString() + "' AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;
					conn.Open();
					cmd = new SqlCommand(reqSQL,conn);
					try
					{
						SqlDataReader sqlDR= cmd.ExecuteReader();
						if (sqlDR.HasRows)//Co du lieu
						{
							sqlDR.Read();
							if (int.Parse(sqlDR[0].ToString()) > 3)
							{
								res = "Ban da vuot qua gioi han tin nhan trong 30 phut";
								cmd.Dispose();
								conn.Close();
								sqlDR.Close();
								return res;
							}
						}
						sqlDR.Close();
					}
					catch (Exception ex)
					{
						ServiceID = ex.Message;
					}   
					finally
					{
					
						cmd.Dispose();

					}


					//Kiem tra mot ngay 
					reqSQL = "(SELECT MO_ID From SMS_MT WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' and charindex('gioi han tin nhan',Message)>0 )";
					reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID = '" + UserNumber + "' AND ServiceID = '" + ServiceID + "' AND CommandCode ='" + CommandCode + "' AND MO_ID not in " + reqSQL;

					cmd = new SqlCommand(reqSQL,conn);
					try
					{
						SqlDataReader sqlDR= cmd.ExecuteReader();
						if (sqlDR.HasRows)//Co du lieu
						{
							sqlDR.Read();
							if (int.Parse(sqlDR[0].ToString()) > 10)
							{
								res = "Ban da vuot qua gioi han tin nhan trong ngay";
								cmd.Dispose();
								conn.Close();
								sqlDR.Close();
								return res;
							}
						}
						sqlDR.Close();
					}
					catch (Exception ex)
					{
						ServiceID = ex.Message;
					}   
					finally
					{
					
						cmd.Dispose();
						conn.Close();
					}
				}
			}
			return res;
		}

		/*public bool OverLimit(string UserID)
		{
			SMS_STR smsSTR = new SMS_STR();
			string UserNumber = smsSTR.ValidMobileNum(UserID);
			if (UserNumber == "") return false;
			bool Limit = false;
			bool isViettel = UserNumber.Substring(0,4) == "8497" || UserNumber.Substring(0,4) == "8498" || UserNumber.Substring(0,5) == "84168";
			int TotalValue = 0;
			int ViettelLowLimit = 0;
			int ViettelHighLimit = 0;
			string ServiceID;
			SqlConnection conn;
			string connstr = ConfigSettings.ReadSetting("SMSConnStr");
			conn = new SqlConnection(connstr);
			conn.Open();

			//Kiem tra trong 5 phut nhan qua 3 tin
			string reqSQL = "SELECT Count(*) AS Total FROM SMS_MO WHERE RequestTime >= '" + DateTime.Now.AddMinutes(-5).ToString() + "' AND UserID = '" + UserNumber + "'";
			SqlCommand cmd = new SqlCommand(reqSQL, conn);

			try
			{
				SqlDataReader sqlDR = cmd.ExecuteReader();
				if (sqlDR.HasRows)
				{
					sqlDR.Read();
					if (int.Parse(sqlDR[0].ToString()) > 3) // Qua 3 tin nhan
					{
						//Limit = true;
						cmd.Dispose();
						conn.Close();
						return true;
					}
				}
			}
			catch (Exception ex)
			{
				cmd.Dispose();
				conn.Close();
				return false;
			}   
			finally
			{

			}
			
			reqSQL = "SELECT ServiceID, COUNT(*) AS Total FROM SMS_MO WHERE (RequestTime >= CONVERT(varchar, GETDATE(), 111)) AND UserID='" + UserNumber + "' GROUP BY ServiceID";
			cmd.Dispose();
			conn.Close();
			conn.Open();
			cmd = new SqlCommand(reqSQL,conn);
			try
			{
				SqlDataReader sqlDR= cmd.ExecuteReader();
				
				
				if (sqlDR.HasRows)//Co du lieu
				{
					while (sqlDR.Read())
					{
						ServiceID = sqlDR[0].ToString();
						if (isViettel) //Viettel
						{
							if (ServiceID.IndexOf("8059")>=0 || ServiceID.IndexOf("8159")>=0 || ServiceID.IndexOf("8259")>=0 || ServiceID.IndexOf("8359")>=0 || ServiceID.IndexOf("8459")>=0 || ServiceID.IndexOf("8559")>=0)
								ViettelLowLimit = ViettelLowLimit + int.Parse(sqlDR[1].ToString());
							else
								ViettelHighLimit = ViettelHighLimit + int.Parse(sqlDR[1].ToString());
						}
						else //Vina-Mobile
						{
							if (ServiceID.IndexOf("8059")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 500;
							}
							if (ServiceID.IndexOf("8159")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 1000;
							}
							if (ServiceID.IndexOf("8259")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 2000;
							}						
							if (ServiceID.IndexOf("8359")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 3000;
							}						
							if (ServiceID.IndexOf("8459")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 4000;
							}							
							if (ServiceID.IndexOf("8559")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 5000;
							}							
							if (ServiceID.IndexOf("8659")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 10000;
							}							
							if (ServiceID.IndexOf("8759")>=0)
							{
								TotalValue = TotalValue + int.Parse(sqlDR[1].ToString()) * 15000;
							}							
						}
					}                   
					if (ViettelHighLimit > 10 || ViettelLowLimit > 100 || TotalValue > 150000)
						Limit = true;
				}
				sqlDR.Close();
			}
			catch (Exception ex)
			{
				ServiceID = ex.Message;
			}   
			finally
			{
				cmd.Dispose();
				conn.Close();
			}
			return Limit;
		}*/
	}
}
